package com.yjd.bi.report.util;

import java.util.List;
import java.util.Map;

import org.apache.tomcat.jdbc.pool.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.jdbc.support.rowset.SqlRowSetMetaData;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;

import com.yjd.bi.report.model.BiDatabaseInfoModel;
import com.yjd.comm.util.FrameUtil;
import com.yjd.comm.util.StringUtil;

public class BiUtil {

	@SuppressWarnings("unchecked")
	private static Map<String, javax.sql.DataSource> dataSourceCache = FrameUtil
			.newHashMap();
	@SuppressWarnings("unchecked")
	private static Map<String, JdbcTemplate> jdbcTemplateMap = FrameUtil
			.newHashMap();

	/*
	 * private static JdbcTemplate jt = null; static { DataSource dataSource =
	 * new DataSource(); dataSource.setDriverClassName("com.mysql.jdbc.Driver");
	 * dataSource
	 * .setUrl("jdbc:mysql://localhost:3306/test?characterEncoding=UTF-8");
	 * dataSource.setUsername("root"); dataSource.setPassword("root");
	 * dataSource.setInitialSize(5); dataSource.setMaxActive(300);
	 * dataSource.setMinIdle(5); dataSource.setMaxIdle(20);
	 * dataSource.setTestOnBorrow(true); dataSource.setTestOnReturn(false);
	 * dataSource.setTestWhileIdle(true);
	 * dataSource.setValidationQuery("select 1"); dataSource.setMaxWait(5000);
	 * dataSource.setTimeBetweenEvictionRunsMillis(600000);
	 * dataSource.setMinEvictableIdleTimeMillis(1800000); jt = new
	 * JdbcTemplate(dataSource); }
	 */

	/**
	 * 获取jdbcTemplate
	 * 
	 * @param m
	 * @return
	 */
	public static JdbcTemplate getJdbcTemplate(BiDatabaseInfoModel m) {
		if (!validata(m)) {
			FrameUtil.throwBaseException1("biDatabaseInfoModel中jdbc_driver、jdbc_url不能为空!");
		}
		String key = getCacheKey("dataSource", m);
		JdbcTemplate jt = jdbcTemplateMap.get(key);
		if (jt == null) {
			synchronized (BiUtil.class) {
				if (jt == null) {
					DataSource dataSource = new DataSource();
					dataSource.setDriverClassName(m.getJdbc_driver());
					dataSource.setUrl(m.getJdbc_url());
					dataSource.setUsername(m.getJdbc_username());
					dataSource.setPassword(m.getJdbc_password());
					dataSource.setInitialSize(5);
					dataSource.setMaxActive(300);
					dataSource.setMinIdle(5);
					dataSource.setMaxIdle(20);
					dataSource.setTestOnBorrow(true);
					dataSource.setTestOnReturn(false);
					dataSource.setTestWhileIdle(true);
					dataSource.setValidationQuery("select 1");
					dataSource.setMaxWait(5000);
					dataSource.setTimeBetweenEvictionRunsMillis(600000);
					dataSource.setMinEvictableIdleTimeMillis(1800000);
					jt = new JdbcTemplate(dataSource);
					jdbcTemplateMap.put(key, jt);
				}
			}
		}
		return jt;
	}

	/**
	 * 判断BiDatabaseInfoModel是否合法
	 * 
	 * @param m
	 * @return
	 */
	public static boolean validata(BiDatabaseInfoModel m) {
		if (m == null || StringUtil.isEmpty(m.getJdbc_driver())
				|| StringUtil.isEmpty(m.getJdbc_url())) {
			return false;
		} else {
			return true;
		}
	}

	public static javax.sql.DataSource getDataSource(BiDatabaseInfoModel m) {
		if (!validata(m)) {
			FrameUtil
					.throwBaseException1("biDatabaseInfoModel中jdbc_driver、jdbc_url不能为空!");
		}
		String key = getCacheKey("dataSource", m);
		javax.sql.DataSource ds = dataSourceCache.get(key);
		if (ds == null) {
			synchronized (BiUtil.class) {
				if (ds == null) {
					ds = getDataSource(m.getJdbc_driver(), m.getJdbc_url(),
							m.getJdbc_username(), m.getJdbc_password());
					dataSourceCache.put(key, ds);
				}
			}
		}
		return ds;
	}

	/**
	 * 获取对应的cache的key
	 * 
	 * @param m
	 * @return
	 */
	private static String getCacheKey(String prex, BiDatabaseInfoModel m) {
		String key = String.format(
				"%s-id:%s-driver:%s-url:%s-username:%s-passord:%s", prex,
				m.getId(), m.getJdbc_driver(), m.getJdbc_url(),
				m.getJdbc_username(), m.getJdbc_password());
		return key;
	}

	/**
	 * 获取数据源
	 * 
	 * @param driver
	 *            jdbc驱动
	 * @param url
	 *            jdbc链接url
	 * @param username
	 *            jdbc链接用户名
	 * @param password
	 *            jdbc链接密码
	 * @return
	 */
	public static javax.sql.DataSource getDataSource(String driver, String url,
			String username, String password) {
		if (StringUtil.isEmpty(driver) || StringUtil.isEmpty(url)
				|| StringUtil.isEmpty(username) || StringUtil.isEmpty(password)) {
			FrameUtil
					.throwBaseException1("jdbc driver、url、username、password不能为空!");
		}
		DataSource dataSource = new DataSource();
		dataSource.setDriverClassName(driver);
		dataSource.setUrl(url);
		dataSource.setUsername(username);
		dataSource.setPassword(password);
		dataSource.setInitialSize(5);
		dataSource.setMaxActive(300);
		dataSource.setMinIdle(5);
		dataSource.setMaxIdle(20);
		dataSource.setTestOnBorrow(true);
		dataSource.setTestOnReturn(false);
		dataSource.setTestWhileIdle(true);
		dataSource.setValidationQuery("select 1");
		dataSource.setMaxWait(5000);
		dataSource.setTimeBetweenEvictionRunsMillis(600000);
		dataSource.setMinEvictableIdleTimeMillis(1800000);
		return dataSource;
	}

	/**
	 * 包含事务的处理
	 * 
	 * @param m
	 * @param execute
	 * @return
	 * @throws Exception
	 */
	public static <T> T execute(BiDatabaseInfoModel m, ITransExecute<T> execute)
			throws Exception {
		T result = null;
		JdbcTemplate jt = BiUtil.getJdbcTemplate(m);
		DataSourceTransactionManager transactionManager = new DataSourceTransactionManager(
				jt.getDataSource());
		DefaultTransactionDefinition def = new DefaultTransactionDefinition();
		TransactionStatus status = transactionManager.getTransaction(def);
		try {
			result = execute.execute(jt);
			transactionManager.commit(status);
		} catch (Exception e) {
			transactionManager.rollback(status);
			throw e;
		}
		return result;
	}

	/**
	 * 获取表的列明列表
	 * 
	 * @param tablename
	 * @param jt
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public static List<String> getColumnNames(String tablename, JdbcTemplate jt) {
		if (StringUtil.isEmpty(tablename) || jt == null) {
			return null;
		}
		List<String> columnNames = FrameUtil.newArrayList();
		SqlRowSet sqlRowSet = jt.queryForRowSet(String.format(
				"select * from %s limit 0", tablename));
		SqlRowSetMetaData sqlRsmd = sqlRowSet.getMetaData();
		int columnCount = sqlRsmd.getColumnCount();
		for (int i = 1; i <= columnCount; i++) {
			columnNames.add(sqlRsmd.getColumnName(i).trim());
		}
		return columnNames;
	}

	public static interface ITransExecute<T> {
		public T execute(JdbcTemplate jt) throws Exception;
	}

}
